﻿/****************************** Module Header ******************************\
 * Module Name:  IDatabaseHelper.cs
 * Module Version:  1.0.0 (2013-6-5)
 * Copyright (c) AnyEB.Com (14507247@qq.com)
 * 
 * This class is used to operate database. It supplies public method to execute 
 * a sql command or get a common connection or get the database schema infomation.
 * 
 * This source is subject to the GPL License.
 * See http://www.gnu.org/licenses/gpl.html.
 * All other rights reserved.
 * 
 * THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, 
 * EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED 
 * WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE.
\***************************************************************************/
using System;
using System.Data;
using System.Data.Common;
using System.Collections.Generic;

namespace UCL.Data
{
    /// <summary>
    /// 数据库帮助类接口
    /// </summary>
    /// <remarks>
    /// <para>2013-6-5 by sutroon created</para>
    /// <para>2014-1-23 1.0.1 by sutroon 
    ///     1.IDatabaseHelper重构，原来Adv_开头的方法改为SO_开头(SuperOperate)，原来Sql_开头的方法改为SQL_开头，表示返回的是Sql模板字符串。
    ///     2.IDatabaseHelper新增获取新建存储过程、新建视图、重命名视图、重命名存储过程等Sql模板字符串的方法
    ///     </para>
    /// <para>2015-1-21 1.0.2 by sutroon 
    ///     1.DatabaseName,ServerName,ConnectionString,ProviderName属性合并到ConnectionInfo结构中, 新增ConnInfo属性
    ///     2.SQL_Table,SQL_Procedure范例语句合并到SqlTemplate结构中,新增SqlTemplate属性
    ///     3.SO高级操作方法提取到新增的接口文件IAdvancedDatabaseHelper中</para>
    /// <para></para>
    /// </remarks>
    public interface IDatabaseHelper
    {
        /// <summary>
        /// 获取或设置数据库连接信息
        /// </summary>
        ConnectionInfo ConnInfo { get; }

        /// <summary>
        /// 获取数据库SQL语句模板
        /// </summary>
        SqlTemplate SqlTemplate { get; }

        /// <summary>
        /// 获取或设置当前操作的数据表名称
        /// </summary>
        string TableName { get; set; }

        /// <summary>
        /// 获取或设置连接超时时间
        /// </summary>
        int Timeout { get; set; }

        /// <summary>
        /// 获取或设置是否调试模式的变量
        /// </summary>
        bool IsDebug { get; set; }

        /// <summary>
        /// 获取或设置帮助类中的消息列表
        /// </summary>
        IList<string> MessageSet { get; set; }

        /// <summary>
        /// 获取连接字符串
        /// </summary>
        /// <param name="serverName"></param>
        /// <param name="loginID"></param>
        /// <param name="password"></param>
        /// <param name="database"></param>
        /// <returns></returns>
        string GetConnectionString(string serverName, string loginID, string password, string database = "");

        /// <summary>
        /// 获取通用DbCommand对象
        /// </summary>
        /// <returns></returns>
        DbCommand GetDbCommand();

        /// <summary>
        /// 获取通用DbConnection对象
        /// </summary>
        /// <returns></returns>
        DbConnection GetDbConnection();

        #region 普通CURD等操作方法

        /// <summary>
        /// 执行Sql语句并返回DataSet对象
        /// </summary>
        /// <param name="commandText"></param>
        /// <param name="paras"></param>
        /// <returns></returns>
        DataSet ExecuteDataSet(string commandText, DbParameter[] paras = null);

        /// <summary>
        /// 执行Sql语句并返回DataTable对象
        /// </summary>
        /// <param name="commandText"></param>
        /// <param name="paras"></param>
        /// <returns></returns>
        DataTable ExecuteDataTable(string commandText, DbParameter[] paras = null);

        /// <summary>
        /// 执行分页Sql命令并返回DataTable对象
        /// </summary>
        /// <param name="fromsql"></param>
        /// <param name="orderby"></param>
        /// <param name="fields"></param>
        /// <param name="recordcount"></param>
        /// <param name="pagecount"></param>
        /// <param name="pageid"></param>
        /// <param name="pagesize"></param>
        /// <returns></returns>
        DataTable ExecutePagingDataTable(string fromsql, string orderby, string fields, out int recordcount, out int pagecount, int pageid = 1, int pagesize = 20);

        /// <summary>
        /// 执行Sql语句并返回受影响的函数(无需返回值,用于Insert,Update,Delete等命令)
        /// </summary>
        /// <param name="commandText"></param>
        /// <param name="paras"></param>
        /// <returns></returns>
        int ExecuteNonQuery(string commandText, DbParameter[] paras = null);

        /// <summary>
        /// 执行存储过程并返回DataTable对象
        /// </summary>
        /// <param name="procedureName"></param>
        /// <param name="dbParameters"></param>
        /// <returns></returns>
        DataTable ExecuteProcedureDataTable(string procedureName, params DbParameter[] dbParameters);

        /// <summary>
        /// 执行存储过程并返回DataTable对象
        /// </summary>
        /// <param name="procedureName"></param>
        /// <param name="outParameters"></param>
        /// <param name="dbParameters"></param>
        /// <returns></returns>
        DataTable ExecuteProcedureDataTable(string procedureName, ref Dictionary<string, object> outParameters, params DbParameter[] dbParameters);

        /// <summary>
        /// 执行存储过程且无需返回值
        /// </summary>
        /// <param name="procedureName"></param>
        /// <param name="dbParameters"></param>
        /// <returns></returns>
        Dictionary<string, object> ExecuteProcedureNonQuery(string procedureName, params DbParameter[] dbParameters);

        /// <summary>
        /// 递归获取分类数据表,返回包含树形层级标识的DataTable
        /// <para>DataTable数据主要用于DropDownList控件</para>
        /// </summary>
        /// <param name="sqlQuery">查询语句,如：select id,name from catalog where parentid={0} and extags='album' order by Ordinal</param>
        /// <param name="parentID">父编号,一般是0</param>
        /// <param name="paddingChar">名称前面分级填充的字符，如 '_'</param>
        /// <param name="loopCount">递归次数,默认值是0</param>
        /// <param name="topID"></param>
        /// <param name="topName"></param>
        /// <returns></returns>
        DataTable ExecuteTreeTable(string sqlQuery, string parentID, char paddingChar = '_', int loopCount = 0, string topID = "0", string topName = "请选择");

        /// <summary>
        /// 递归获取分类数据表,数据主要用于DropDownList控件 !@c 2014-5-16
        /// </summary>
        /// <param name="sqlQuery">查询语句，如<para>select ID,Name from catalog where parentid={0} and innercat='album' order by Ordinal</para></param>
        /// <param name="parentID">父编号</param>
        /// <param name="dataTable">用户填充数据的空数据表</param>
        /// <param name="paddingChar">名称前面分级填充的字符，如 '-'</param>
        /// <param name="loopCount">递归次数,默认值是0</param>
        // void ExecuteTreeList(string sqlQuery, string parentID, ref DataTable dataTable, char paddingChar, int loopCount);

        /// <summary>
        /// 递归获取当前节点的所有子节点(包含节点关联的元素)和递归父节点树形列表
        /// <para>2013-9-5 by sutroon created</para>
        /// <para>2013-9-9 by sutroon 重构，新增父级链表重塑和子集项目关联</para>
        /// <para>例如：ExecuteTreeXml("Select ID, Name from tUSerDepartment where ParentID='{0}'", "093F207D-634B-4717-AB50-91A21F035083", "Select AgentID, Name from tUserMember where DepartmentID='{0}'", "select ID, Name, ParentID from tUserDepartment where ID='{0}'"));</para>
        /// </summary>
        /// <param name="sqlChildFmt">如：Select ID, Name from tUSerDepartment where ParentID='{0}'</param>
        /// <param name="nodeID">当前节点的值</param>
        /// <param name="sqlfmtItems">如：Select AgentID, Name from tUserMember where DepartmentID='{0}' (DepartmentID是外键，对应部门表的主键)</param>
        /// <param name="sqlfmtParent">如：select ID, Name, ParentID from tUserDepartment where ID='{0}' (需要通过Parent来追溯父级链，并且ParentID必需是第三个查询的字段！)</param>
        /// <returns></returns>
        string ExecuteTreeXml(string sqlChildFmt, string nodeID, string sqlfmtItems = "", string sqlfmtParent = "");
        
        /// <summary>
        /// 获取所有父级编号列表(包括自己)
        /// </summary>
        /// <param name="sqltpl">如: select ParentID from tTable where ID='{0}' order by Ordinal</param>
        /// <param name="id"></param>
        /// <returns></returns>
        IList<string> GetParentIds(string sqltpl, string id = "");

        /// <summary>
        /// 获取所有子级编号列表(不包括自己)
        /// </summary>
        /// <param name="sqltpl">如：select ID from tTable where ParentID='{0}'</param>
        /// <param name="id"></param>
        /// <returns></returns>
        /// <remarks>2015-1-29 by sutroon</remarks>
        IList<string> GetChildIds(string sqltpl, string id = "");

        /// <summary>
        /// 获取单列的所有数据列表
        /// </summary>
        /// <typeparam name="V"></typeparam>
        /// <param name="search"></param>
        /// <returns></returns>
        IList<V> GetColumnValues<V>(string search);

        /// <summary>
        /// 获取单元格的值
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="defv"></param>
        /// <returns></returns>
        object GetValue(string sql, object defv = null);

        /// <summary>
        /// 测试连接是否有效
        /// </summary>
        /// <param name="message"></param>
        /// <returns></returns>
        bool TestConnection(out string message);

        /// <summary>
        /// 更新数据表的记录
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="fields"></param>
        /// <param name="search"></param>
        /// <returns></returns>
        int Update(string tableName, string fields, string search);

        /// <summary>
        /// 向数据表插入记录
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="fields"></param>
        /// <param name="values"></param>
        /// <returns></returns>
        int Insert(string tableName, string fields, string values);

        /// <summary>
        /// 删除数据表记录
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="search"></param>
        /// <returns></returns>
        int Delete(string tableName, string search);

        /// <summary>
        /// 判断数据表中是否存在符合条件的记录
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="search"></param>
        /// <param name="field"></param>
        /// <returns></returns>
        bool Exists(string tableName, string search, string field = "ID");

        /// <summary>
        /// 查询数据表中符合条件的记录数量
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="search"></param>
        /// <param name="field"></param>
        /// <returns></returns>
        int Count(string tableName, string search, string field = "ID");

        /// <summary>
        /// 查询数据表中的记录,返回DataTable对象
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="search"></param>
        /// <param name="fields"></param>
        /// <param name="count"></param>
        /// <returns></returns>
        DataTable SelectDataTable(string tableName, string search, string fields = "*", int count=0);

        #endregion

        /// <summary>
        /// 设置limit条件
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="limit"></param>
        /// <returns></returns>
        string SetLimitSql(string sql, string limit);

        /// <summary>
        /// 格式化SQL
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        string FormatSql(string sql);

    }
}
